package com.deliveryservicesystem.service.impl;

import com.deliveryservicesystem.app.util.AppUtils;
import com.deliveryservicesystem.dataaccess.DatabaseConnection;
import com.deliveryservicesystem.model.Customer;
import com.deliveryservicesystem.model.Delivery;
import com.deliveryservicesystem.model.Subscription;
import com.deliveryservicesystem.service.Report1;
import com.deliveryservicesystem.service.crud.impl.CustomerCrudServiceImpl;
import java.sql.*;
import java.text.ParseException;
import java.util.Date;
import java.util.List;
import java.util.Locale;

/**
 * File Name: Report1Impl.java
 * Version:
 * Description:
 * Package: com.deliveryservicesystem.service.impl
 * Author: Lance
 * Date: Jul 28, 2012
 * Editor:
 */
public class Report1Impl implements Report1
{

    @Override
    public ResultSet getDeliveryDriver(String driverNo, Date deliveryFromDate, Date deliveryToDate) throws SQLException, ParseException
    {
        String fromDate = AppUtils.toLocaleDate(deliveryFromDate, "dd/MM/yyyy", Locale.ENGLISH);
        String toDate = AppUtils.toLocaleDate(deliveryToDate, "dd/MM/yyyy", Locale.ENGLISH);
        //String dateFormatConverter = AppUtils.dateFormatConverter("30/05/2011", "dd/mm/yyyy", "dd/mm/yyyy", Locale.ENGLISH);

        StringBuilder stringBuilder = new StringBuilder("SELECT * FROM DELIVERY WHERE DRIVER_NO = ");
        stringBuilder.append(driverNo);
        stringBuilder.append(" AND DELIVERY_DATE >= TO_DATE( '").append(fromDate);
        stringBuilder.append("', 'dd/mm/yyyy') AND DELIVERY_DATE < TO_DATE('").append(toDate);
        stringBuilder.append("', 'dd/mm/yyyy') + 1");

        DatabaseConnection databaseConnection = DatabaseConnection.getInstance();
        Connection connection = databaseConnection.getConnection();
        Statement createStatement = connection.createStatement();
        ResultSet resultSet = createStatement.executeQuery(stringBuilder.toString());
        return resultSet;
    }

    @Override
    public String formatResultSet(ResultSet resultSet) throws SQLException
    {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int numberOfColumns = metaData.getColumnCount();
        StringBuilder builder = new StringBuilder();

        for (int i = 1; i <= numberOfColumns; i++)
        {
            if (i != 2)
            {
                String columnName = metaData.getColumnName(i);
                builder.append(columnName).append("\t");
            }
        }
        builder.append("").append("Customer");
        builder.append("\n");

        int totalDeliveries = 0;
        int notDelivered = 0;
        int delivered = 0;
        while (resultSet.next())
        {
            String string = resultSet.getString("DELIVERY_NO");
            Customer c = getCustomerByDeliveryNo(string);
            if ("1".equals(resultSet.getString("IS_DELIVERED")))
            {
                delivered++;
            }
            else
            {
                notDelivered++;
            }
            for (int i = 1; i <= numberOfColumns; i++)
            {
                if (i != 2)
                {
                    String columnValue = resultSet.getString(i);
                    builder.append(columnValue).append("\t\t");
                }
            }
            if (c != null)
            {
                builder.append("\t").append(c.getCustomerNo().toString()).append(" ").append(c.getFirstName().substring(0, 1)).append(". ").append(c.getLastName());
            }
            totalDeliveries++;
            builder.append("\n");
        }
        builder.append("\nTotal deliveries: ").append(totalDeliveries).append("\nTotal delivered: ").append(delivered).append("\nTotal not delivered: ").append(notDelivered);
        return builder.toString();
    }

    private Customer getCustomerByDeliveryNo(String deliveryNo) throws SQLException
    {
        CustomerCrudServiceImpl crudService = new CustomerCrudServiceImpl();
        List<Customer> customers = crudService.findEntities();
        for (Customer customer : customers)
        {
            List<Subscription> subscriptionList = customer.getSubscriptionList();
            for (Subscription subscription : subscriptionList)
            {
                Delivery delivery = subscription.getDeliveryNo();
                Integer deliveryNumber = delivery.getDeliveryNo();
                if (deliveryNumber == Integer.parseInt(deliveryNo))
                {
                    return customer;
                }
            }
        }
        return null;
    }
}
